The Bigger Picture

In this document we learn how to create interactive tables with DT. Simply put, we are learning how to transform tidy data into visually clear tables. In the overall context of the workflow, this falls into the category of transforming our data into data visualisation.

 

{{<expand "Note: LinkedIn Learning videos" "...">}} There are references to LinkedIn Learning videos. These are complementary but not really required as the notes below are meant to be self-contained. Some students and staff would have access for free. Do not purchase access unless you are sure you don’t have access through your organisation already. {{</expand>}}

What is DT?

Lynda 6.1

library("tidyverse")
library("DT")

Creating Interactive Tables

Lynda 6.2

Let’s say we have a dataset, such as the set below from the ACORN-SAT dataset. Note that we are deliberately using a large dataset (this one contains around 6000 observations)

load("tidy_ACORN-SAT_data/station_data.rdata")
head(station_data, 8)
##   Number year average.temp  Station.name Latitude Longitude Elevation Start
## 1   2012 1910         26.4   Halls Creek   -18.23    127.66       422  1910
## 2   3003 1910         27.3        Broome   -17.95    122.24         7  1910
## 3   6011 1910         21.5     Carnarvon   -24.89    113.67         4  1910
## 4   7045 1910         21.8   Meekatharra   -26.61    118.54       517  1926
## 5   9021 1910         18.0 Perth Airport   -31.93    115.98        15  1910
## 6   9518 1910         16.3  Cape Leeuwin   -34.37    115.14        13  1910
## 7   9789 1910         16.7     Esperance   -33.83    121.89        25  1910
## 8  10917 1910         15.0     Wandering   -32.67    116.67       275  1910

With DT, we can instantly make this an interactive table by using the pipeline (%>%) operator and piping our data into the datatable() function.

station_data %>%
  datatable()

We immediately have an interactive table. The remainder of this tutorial will address how to tweak the table we have. Currently here are some of the things we can do:

Additional Options

We notice that the first ‘column’ is just a number representing which observation of the table we are looking at. This is generally of no purpose to us, so we can remove it using the rownames argument of datatable(). We set it to FALSE.

station_data %>%
  datatable(rownames = FALSE)

We also notice some of our column names are not very aesthetic. We can make them more so by:

  • Using the gsub() and stringr::str_to_title() functions to create a list of nicer names
  • Using the colnames argument of datatable() to change these names

The following command replaces all dots (.s) with spaces, then capitalises the first letter of each word, and stores the new set of names in the column_title variable.

column_titles <- gsub("[.]", " ", colnames(station_data)) %>%
  stringr::str_to_title()

column_titles
## [1] "Number"       "Year"         "Average Temp" "Station Name" "Latitude"    
## [6] "Longitude"    "Elevation"    "Start"

We now use these as the new column names with colnames:

station_data %>%
  datatable(rownames = FALSE,
            colnames = column_titles)

We can already search for a given term in our data, but if we want to search for a term in a single column, we can add individual column filters using the filter argument.

  • This argument takes a list of sub-arguments as its value
  • We set the position sub-argument to “top” or “bottom” to add our filter
station_data %>%
  datatable(rownames = FALSE,
            colnames = column_titles,
            filter = list(position = "top"))

If we wish to display a number of observations which is not 10, 25, 50 or 100, we can use the options argument,

  • This argument takes a list of sub-arguments as its value
  • We set the pageLength sub-argument to the number of observations we wish to display
station_data %>%
  datatable(rownames = FALSE,
            colnames = column_titles,
            options = list(pageLength = 7))

Now say we wish to modify the search bar so that it doesn’t say “Search:”, but instead “Keyword look-up:”.

  • We still use the options argument of datatable()
  • We use the sub-argument language of options, which itself takes a list as its argument
  • We use the sub-sub-argument sSearch to rename the search bar
station_data %>%
  datatable(rownames = FALSE,
            colnames = column_titles,
            options = list(pageLength = 7,
                           language = list(sSearch = "Keyword look-up:")))

Formatting Values in Tables

Lynda 6.3

Sometimes we will wish to display data in specific formats, such as dollars ($), a percentage (%) or otherwise. We have specific functions in DT for each of these.

sample_price %>%
  datatable()

If we wish to format a column as a currency, we pipe our table into formatCurrency():

sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "$")

If we were dealing in another currency, such as the Japanese Yen, we can specify an alternate currency:

sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "¥")

If we wish to change the decimal places displayed, we use the additional argument digits:

sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "$") %>%
  formatCurrency("Budget",
                 currency = "$",
                 digits = 0)

Let’s say we now want to display the percentage of our budget we have spent.

sample_price <- sample_price %>%
  mutate(Portion_Spent = Amount_Spent / Budget)
sample_price
## # A tibble: 6 x 4
##   Date       Budget Amount_Spent Portion_Spent
##   <chr>       <dbl>        <dbl>         <dbl>
## 1 21-06-2019     50         49.2         0.984
## 2 22-06-2019     50         47.2         0.944
## 3 23-06-2019     50         41.8         0.836
## 4 24-06-2019     50         52.0         1.04 
## 5 25-06-2019     50         52.9         1.06 
## 6 26-06-2019     50         37.8         0.755

We use the formatPercentage() function, specifying:

sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "$") %>%
  formatCurrency("Budget",
                 currency = "$",
                 digits = 0) %>%
  formatPercentage("Portion_Spent",
                 digits = 2)

Lastly, if we wish to format dates, we use the formatDate() function:

sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "$") %>%
  formatCurrency("Budget",
                 currency = "$",
                 digits = 0) %>%
  formatPercentage("Portion_Spent",
                 digits = 2) %>%
  formatDate("Date",
             method = "toDateString")

Here the date formating failed. The reason for this is that the “Date” column contains strings of D-M-Y formatted dates. The formatDate() function requires entries of class ‘date’, not strings. We can fix this using the lubridate package.

class(sample_price$Date)
## [1] "character"
library("lubridate")
sample_price <- sample_price %>%
  mutate(Date = dmy(Date))

class(sample_price$Date)
## [1] "Date"
sample_price %>%
  datatable() %>%
  formatCurrency("Amount_Spent",
                 currency = "$") %>%
  formatCurrency("Budget",
                 currency = "$",
                 digits = 0) %>%
  formatPercentage("Portion_Spent",
                 digits = 2) %>%
  formatDate("Date",
             method = "toDateString")

Options for Responsive Tables

Lynda 6.4

If we have tables with lots of columns, we have options to truncate these.

(Sample data provided by Climate Change in Australia)

fire_data %>%
  datatable(rownames = FALSE,
            options = list(pageLength = 5))

The simplest method is using the argument extensions of datatable().

fire_data %>%
  datatable(rownames = FALSE,
            extensions = "Responsive",
            options = list(pageLength = 5))

We now have a + button that displays columns which can’t fit on our screen. There is, however, an even more responsive solution which allows the reader to select and deselect columns for viewing as they like. This requires some setup:

fire_data %>%
  datatable(rownames = FALSE,
            extensions = c("Responsive", "Buttons"),
            options = list(pageLength = 5,
                           buttons = I("colvis"),
                           dom = "Bf"))

We now have a “Column visibility” button we can use to toggle columns as displayed and hidden.

More on the dom Argument

This argument controls much about what we see in our tables. The argument is always a string, but, unlike many arguments we have seen in R, it is the individual letters of the string that are important.

In the above example, “Bf” means we desire Buttons and filter to be enabled, in that order. There are actually many letters we can use to enhance or change our table (several of which are described here). We may be interested in:

Letter Function
l Enables length changing control
f Enables the global filter
t The table itself (*)
i Table information summary
p Page number navigation
B Buttons (assuming they have been properly set up)

(*) Of course the table is always enabled, but this option is important in controlling order. For example, if we want the filter to be below the table, we use dom = "tf":

station_data %>%
  datatable(rownames = FALSE,
            options = list(pageLength = 5,
                           dom = "tf"))

As another example, consider the argument “Bpiltf”, structuring the widget, from top to bottom, as buttons, page-navigation, summary info, length changing, the table, and the filter below:

station_data %>%
  datatable(rownames = FALSE,
            extensions = c("Responsive", "Buttons"),
            options = list(pageLength = 5,
                           buttons = I("colvis"),
                           dom = "Bpiltf"))

Lastly, as a fun example, consider that we are not limited to just one of each letter:

station_data %>%
  datatable(rownames = FALSE,
            options = list(pageLength = 5,
                           dom = "ppfptpf"))

Maybe this would not be such a good idea in practice!

Allowing Users to Download Table Data

Lynda 6.5

We can add a button which allows users to download the data as it appears in our table.

To do this is very similar to how we added the “colvis” buttons:

station_data %>%
  datatable(rownames = FALSE,
            extensions = c("Responsive", "Buttons"),
            options = list(pageLength = 5,
                           buttons = c("colvis", "excel", "csv"),
                           dom = "Bf"))